package com.guojian.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.Assert;

import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.parser.CountSqlParser;
import com.guojian.common.view.PageModel;
import com.guojian.common.view.PageView;
import com.guojian.jdbc.filter.SqlFilter;
import com.guojian.jdbc.mapper.BeanPropertyDataFormatRowMapper;
import com.guojian.jdbc.mapper.ColumnMapDataFormatRowMapper;
import com.guojian.jdbc.mapper.SingleColumnRowMapperX;
import com.guojian.jdbc.mapper.StringMapRowMapper;
import com.guojian.jdbc.sql.OracleParser;
import com.guojian.jdbc.sql.SqlParser;
import com.guojian.jdbc.sql.SqlServerParser;
import com.guojian.jdbc.utils.DataAccessUtilsX;

/**
 * guojian JDBC数据操作核心类,增加自动分页,和数据转换
 */

public class JdbcTemplateX extends JdbcTemplate {

	private SqlParser sqlParser;

	public JdbcTemplateX(DataSource dataSource) {
		setDataSource(dataSource);
	}

	public JdbcTemplateX(DataSource dataSource, SqlParser sqlParser) {
		setDataSource(dataSource);
		this.sqlParser = sqlParser;
	}

	private final CountSqlParser csp = new CountSqlParser();

	@Autowired
	private ApplicationContext applicationContext;

	public String filterSql(String sql) {
		String newSql = sql;
		Map<String, SqlFilter> sqlFilterMap = applicationContext.getBeansOfType(SqlFilter.class);
		for (SqlFilter filter : sqlFilterMap.values()) {
			newSql = filter.filter(newSql);
		}
		return newSql;
	}

	/**
	 * web端的分页(根据数据库的类型,自动生成count语句和分页语句)
	 */
	public PageView<Map<String, Object>> queryWebListMapByPage(String sql, PageModel page, Object... params) {
		return getTableDataInfo(sql, page, new ColumnMapDataFormatRowMapper(), params);
	}

	public <T> PageView<T> queryWebListByPage(String sql, PageModel page, Class<T> cType, Object... params) {
		return getTableDataInfo(sql, page, new SingleColumnRowMapperX<>(cType), params);
	}

	public <T> PageView<T> queryWebListBeanByPage(String sql, PageModel page, Class<T> cType, Object... params) {
		return getTableDataInfo(sql, page, new BeanPropertyDataFormatRowMapper<>(cType), params);
	}

	public <T> PageView<T> getTableDataInfo(String sql, PageModel page, RowMapper<T> rowMapper, Object... params) {
		String newSql = filterSql(sql);
		int total = queryForObject(csp.getSmartCountSql(newSql), Integer.class, params);
		Assert.notNull(getSqlParser(), "未配置对应的sqlParser");
		String pageSql = getSqlParser().convertToPageSql(newSql, page.getPageUIStartNum() - 1, page.getRows());
		List<T> rows = super.query(pageSql, rowMapper, params);
		return new PageView<T>(rows, total);
	}

	/**
	 * 移动端分页(不需要计算total,只需要nextPageNum)
	 */
	public List<Map<String, Object>> queryMobileListByPage(String sql, PageModel page, String[] rparams, Object... params) {
		return queryForList(convertToPageSql(sql, page, rparams), params);
	}

	private String convertToPageSql(String sql, PageModel page, String[] rparams) {
		String rsql = sqlParser.convertToPageSql(sql, page.getPageUIStartNum() - 1, page.getRows());
		if (rparams == null || rparams.length == 0) {
			return rsql;
		}
		for (int i = 1; i <= rparams.length; i++) {
			rsql = rsql.replace("##" + i, rparams[i - 1]);
		}
		return rsql;
	}

	@Override
	public Map<String, Object> queryForMap(String sql, Object... args) {
		List<Map<String, Object>> list = queryForList(sql, args);
		return DataAccessUtilsX.getNullableSingleResult(list);
	}

	@Override
	public Map<String, Object> queryForMap(String sql) throws DataAccessException {
		return DataAccessUtilsX.getNullableSingleResult(queryForList(sql));
	}

	@Override
	public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException {
		return super.query(sql, new ArgumentPreparedStatementSetter(args), new ColumnMapDataFormatRowMapper());
	}

	@Override
	public List<Map<String, Object>> queryForList(String sql) throws DataAccessException {
		return super.query(sql, new ColumnMapDataFormatRowMapper());
	}

	public <T> T queryObject(String sql, Class<T> elementType, Object... args) {
		return DataAccessUtilsX.getNullableSingleResult(queryForList(sql, elementType, args));
	}

	public <T> T queryObject(String sql, Class<T> elementType) {
		return DataAccessUtilsX.getNullableSingleResult(queryForList(sql, elementType));
	}

	public Map<String, String> queryMap(String sql, Object... args) {
		return DataAccessUtilsX.getNullableSingleResult(queryList(sql, args));
	}

	public List<Map<String, String>> queryList(String sql, Object... args) {
		return query(sql, new StringMapRowMapper(), args);
	}

	public <T> List<T> queryBeanList(String sql, Class<T> tClass, Object... args) {
		return query(sql, new ArgumentPreparedStatementSetter(args), new BeanPropertyDataFormatRowMapper<>(tClass));
	}

	public <T> T queryBean(String sql, Class<T> tClass, Object... args) {
		return DataAccessUtilsX.getNullableSingleResult(query(sql, new ArgumentPreparedStatementSetter(args), new BeanPropertyDataFormatRowMapper<>(tClass)));
	}

	/**
	 * 插入数据并返回主键
	 *
	 * @return
	 */
	public int insertWithReturnKey(String sql, Object... params) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int rows = update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int i = 1;
				for (int j = 0; j < params.length; j++) {
					ps.setObject(i++, params[j]);
				}
				return ps;
			}
		}, keyHolder);
		if (rows > 0) {
			return keyHolder.getKey().intValue();
		}
		return 0;
	}

	/**
	 * 所有操作成功则返回1
	 *
	 * @param sql
	 * @param batchArgs
	 * @return
	 */
	public int batchUpdateWithAllValid(String sql, List<Object[]> batchArgs) {
		int[] effectRows = super.batchUpdate(sql, batchArgs);
		for (int effectRow : effectRows) {
			if (effectRow == 0) {
				return effectRow;
			}
		}
		return 1;
	}

	@Override
	public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException {
		return super.query(filterSql(sql), pss, rse);
	}

	@Override
	public <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException {
		return super.query(filterSql(sql), rse);
	}

	@Override
	public void afterPropertiesSet() {
		super.afterPropertiesSet();
		setSqlParser();
	}

	@SuppressWarnings("resource")
	protected void setSqlParser() {
		if (getSqlParser() == null) {
			DataSource dataSource = super.getDataSource();
			/*
			 * if (dataSource instanceof DynamicDataSource) { DynamicDataSource
			 * dynamicDataSource = (DynamicDataSource) dataSource; DataSource
			 * determineTargetDataSource =
			 * dynamicDataSource.getDetermineTargetDataSource(); }
			 */
			if (dataSource instanceof DruidDataSource) {
				DruidDataSource druidDataSource = (DruidDataSource) dataSource;
				String driverClassName = druidDataSource.getDriverClassName().toLowerCase();
				if (driverClassName.contains("oracle")) {
					this.sqlParser = new OracleParser();
				} else if (driverClassName.contains("sqlserver")) {
					this.sqlParser = new SqlServerParser();
				} else {
					throw new IllegalArgumentException("不支持当前数据库分页,driverClassName: " + driverClassName);
				}
			}
		}

	}

	public SqlParser getSqlParser() {
		return sqlParser;
	}
}
